﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BLL.Services.WikiPages.Listing;

namespace BLL.Services.WikiPages
{
    public class WikiPageCollection
    {
        private readonly string _connectionString;

        public WikiPageCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public WikiPage GetById(int wikiPageId)
        {
            WikiPage result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.GetById(conn, wikiPageId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetWikiPage(reader);
                    }
                }
            }
            return result;
        }

        public WikiPage GetByPublicUrl(string publicUrl)
        {
            WikiPage result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.GetPageByPublicUrl(conn, publicUrl);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetWikiPage(reader);
                    }
                }
            }
            return result;
        }

        public WikiPageListing GetWikiPagesListing(
           int page, int size, string column, int direction, WikiPageFilter filter)
        {
            var result = new WikiPageListing
            {
                Page = page,
                Size = size,
                Column = column,
                Direction = direction,
                Filter = filter
            };
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.GetListing(
                    conn, page, size, column, direction, filter.Name);
                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                {
                    Direction =
                        ParameterDirection
                        .ReturnValue
                };
                cmd.Parameters.Add(totalCount);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new WikiPageListing.WikiPage();
                        item.WikiPageId = reader.GetInt32(0);
                        item.Name = reader.GetString(1);
                        item.CreationDate = reader.GetDateTime(2).ToString();
                        result.WikiPages.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                       ? (int)totalCount.Value / size
                                       : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        public void Create(WikiPage model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.Insert(
                    conn,
                    model.WikiCategoryId,
                    model.PageName,
                    model.Content,
                    model.CreationDate,
                    model.PublicUrl);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetWikiPage(reader);
                        }
                    }
                }
            }
        }
        
        public void Save(WikiPage model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.Update(
                    conn,
                    model.WikiPageId,
                    model.WikiCategoryId,
                    model.PageName,
                    model.Content,
                    model.CreationDate,
                    model.PublicUrl);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetWikiPage(reader);
                        }
                    }
                }
            }
        }

        public void Delete(int pageId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.Delete(conn, pageId);
                cmd.ExecuteNonQuery();
            }
        }

        private WikiPage GetWikiPage(SqlDataReader reader)
        {
            var wikiPage = new WikiPage
                               {
                                   WikiPageId = reader.GetInt32(0),
                                   WikiCategoryId = reader.GetInt32(1),
                                   PageName = reader.GetString(2),
                                   Content = reader.GetString(3),
                                   CreationDate = reader.GetDateTime(4),
                                   PublicUrl = reader.GetString(5)
                               };
            return wikiPage;
        }

        public IEnumerable<WikiPage> GetSomePagesByWikiCategory(string wikiCategoryName, int page, int size, string sortOption, int sortDirection)
        {
            var result = new List<WikiPage>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.WikiPages.GetWikiPagesByWikiCategory(
                    conn, page, size, sortOption, sortDirection, wikiCategoryName);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new WikiPage
                                       {
                                          WikiPageId = reader.GetInt32(0),
                                          WikiCategoryId = reader.GetInt32(1),
                                          PageName = reader.GetString(2),
                                          Content = reader.GetString(3),
                                          CreationDate = reader.GetDateTime(4),
                                          PublicUrl = reader.GetString(5)
                                       };
                        result.Add(item);
                    }
                }
            }
            return result;
        }
    }
}
